An introduction to ranking

There are two ranking functions in modern Excel, which function identically except for how they treat tied items – RANK.EQ and RANK.AVG.

You can read more about these functions in TOTW #383, but as a reminder: RANK.EQ scores tied items with the same rank for all of them (the highest ranking in the order); RANK.AVG averages the ranks spanned by the tied items.  So for example the highest two items in the above list are the two 7s – RANK.EQ ranks them both 1st before moving on to 3rd place for the one 6 in the range; RANK.AVG averages the ranks of 1 and 2 and shows each with rank 1.5.

Ranking within a group

What we want to do, however, is rank within a specific subgroup – so for example, let’s take this table of customer purchases:

We want to rank the size of purchase by customer with a single function.  How can we do this?

You might be thinking that this is analogous to functions like COUNTIFS or SUMIFS, which do familiar calculations but only considering a certain subset of the data they’re fed.  What we want is essentially a RANKIFS.  But that function doesn’t exist.

For several other _IFS functions that don’t exist, we can make them by using an array formula.  For example, here’s a PRODUCTIFS equivalent:

=PRODUCT(IF(B3:B11="ABC Limited",C3:C11,“”))

(arrays need to be confirmed with Ctrl Shift Enter unless you’re working in Excel 365)

However, this approach doesn’t work for the two ranking functions – because these need a full list to compare the current item to, making a list with blanks for the values from the other customers returns an error.  And we can’t use 0 instead of “” or similar, because 0 is an actual value and will throw out our rankings.  So what can we do?

The answer is deceptively simple – we abandon the ranking functions altogether, and use COUNTIFS instead.

Ultimately, what is a ranking?  It’s a count of how many items are above/below the current value.  So we can use this function:

=COUNTIFS(label range, this row’s label, value range, ">"&this row’s value)+1

Note the +1 so that our ranking starts at 1 rather than at 0.  You can do a reverse-ordered ranking just by flipping > to <.

Here’s the result:

Note that this approach handles ties effectively the same way RANK.EQ does.

Related articles

• Previous post - Power Pivot: Filtering active contracts 
• TOTW index 
• Next time – Data structure good practices!